﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using AlarmManagePlatform.Model.DB;

namespace AlarmManagePlatform.DataAccess
{
    internal class tb_TaskInfo
    {
        internal List<Model.DB.tb_TaskInfo> getNotFinish(string UserId)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                string sqlScript = string.Format("select Id,Title,[Desc],CreateTime from tb_TaskInfo where UserId = '{0}' and Status != 0;", UserId);
                sqlDb = Static.Methods.OpenDataBase();
                DataTable tempDT = sqlDb.RunSqlScript(sqlScript);
                List<Model.DB.tb_TaskInfo> result = new List<Model.DB.tb_TaskInfo>();
                Model.DB.tb_TaskInfo tb_TaskInfo;
                for (int i = 0; i < tempDT.Rows.Count; i++)
                {
                    tb_TaskInfo = new Model.DB.tb_TaskInfo();
                    tb_TaskInfo.Id = tempDT.Rows[i]["Id"].ToString();
                    tb_TaskInfo.Title = tempDT.Rows[i]["Title"].ToString();
                    tb_TaskInfo.Desc = tempDT.Rows[i]["Desc"].ToString();

                    //tb_TaskInfo.CreateTime = DateTime.Parse(tempDT.Rows[i]["CreateTime"].ToString());
                    if (!string.IsNullOrEmpty(tempDT.Rows[i]["CreateTime"].ToString()))
                        tb_TaskInfo.CreateTime = DateTime.Parse(tempDT.Rows[i]["CreateTime"].ToString()).ToString(Static.ParaInfo.DateFormatLong);


                    tb_TaskInfo.TaskDetails = new tb_TaskDetails().get(tb_TaskInfo.Id);
                    result.Add(tb_TaskInfo);
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取任务失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }


        internal Model.DB.tb_TaskInfo GetInfoForId(string id)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.tb_TaskInfo> _Resullt;
            DataTable _DT;

            string _SqlScript = string.Empty;
            try
            {
                _Resullt = new List<Model.DB.tb_TaskInfo>();
                _SqlDb = Static.Methods.OpenDataBase();

                _SqlScript = "      SELECT * FROM  tb_TaskInfo ";

                _SqlScript += $"    Where  [Id] = '{id}' ";

                _DT = _SqlDb.RunSqlScript(_SqlScript);

                _Resullt = DataTableToList(_DT);

                if (_Resullt.Count > 0)
                    return _Resullt[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw new Exception("获取任务信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal List<Model.DB.tb_TaskInfo> GetTaskList(int startIndex, int searchCount, Dictionary<string, string> logisticList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.tb_TaskInfo> _Resullt;
            DataTable _DT;

            string _SqlScript = string.Empty;
            try
            {
                _Resullt = new List<Model.DB.tb_TaskInfo>();
                _SqlDb = Static.Methods.OpenDataBase();

                _SqlScript = "      SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY [Id] ASC) AS Num FROM tb_TaskInfo ";

                _SqlScript += $"    Where  [Id] > '0' ";

                if (logisticList.ContainsKey("BeginTime") && !string.IsNullOrEmpty(logisticList["BeginTime"]))
                {
                    _SqlScript += $"   And [CreateTime] >= '{logisticList["BeginTime"]}'";
                }

                if (logisticList.ContainsKey("FinishTime") && !string.IsNullOrEmpty(logisticList["FinishTime"]))
                {
                    _SqlScript += $"   And [CreateTime] <= '{logisticList["FinishTime"]}'";
                }

                if (logisticList.ContainsKey("TaskNumber") && !string.IsNullOrEmpty(logisticList["TaskNumber"]))
                {
                    _SqlScript += $"   And [Id] Like '%{logisticList["TaskNumber"]}%'";
                }

                if (logisticList.ContainsKey("UserId") && !string.IsNullOrEmpty(logisticList["UserId"]))
                {
                    _SqlScript += $"   And [UserId] = {logisticList["UserId"]}";

                }
                 _SqlScript += $"    )AS TempTable";
                _SqlScript += $"    WHERE Num BETWEEN {startIndex} AND {startIndex + searchCount - 1}";

                _DT = _SqlDb.RunSqlScript(_SqlScript);

                _Resullt = DataTableToList(_DT);

                return _Resullt;
            }
            catch (Exception ex)
            {
                throw new Exception("获取任务信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal int GetInfoCount(Dictionary<string, string> logisticList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            int _Result;
            string _Saclar = string.Empty;
            string _SqlScript;
            try
            {
                _Result = -1;

                _SqlScript = $"Select Count([Id]) From tb_TaskInfo ";

                _SqlScript += $"    Where  [Id] > '0' ";


                if (logisticList.ContainsKey("BeginTime") && !string.IsNullOrEmpty(logisticList["BeginTime"]))
                {
                    _SqlScript += $"   And [CreateTime] >= '{logisticList["BeginTime"]}'";
                }

                if (logisticList.ContainsKey("FinishTime") && !string.IsNullOrEmpty(logisticList["FinishTime"]))
                {
                    _SqlScript += $"   And [CreateTime] <= '{logisticList["FinishTime"]}'";
                }

                if (logisticList.ContainsKey("TaskNumber") && !string.IsNullOrEmpty(logisticList["TaskNumber"]))
                {
                    _SqlScript += $"   And [Id] Like '%{logisticList["TaskNumber"]}%'";
                }

                if (logisticList.ContainsKey("UserId") && !string.IsNullOrEmpty(logisticList["UserId"]))
                {
                    _SqlScript += $"   And [UserId] = {logisticList["UserId"]}";
                }
                

                _SqlDb = Static.Methods.OpenDataBase();

                _Saclar = _SqlDb.RunSqlScriptReturnScalar(_SqlScript);
                if (string.IsNullOrEmpty(_Saclar))
                {
                    _Result = 0;
                }
                else
                {
                    _Result = Convert.ToInt32(_Saclar);
                }

                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取任务数量失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal List<Model.DB.tb_TaskInfo> GetAllInfo()
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            List<Model.DB.tb_TaskInfo> _Resullt;
            DataTable _DT;

            string _SqlScript = string.Empty;
            try
            {
                _Resullt = new List<Model.DB.tb_TaskInfo>();
                _SqlDb = Static.Methods.OpenDataBase();

                _SqlScript += "Select * From tb_TaskInfo";
                _DT = _SqlDb.RunSqlScript(_SqlScript);
                
                _Resullt = DataTableToList(_DT);

                return _Resullt;
            }
            catch (Exception ex)
            {
                throw new Exception("获取任务信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal void AddTaskInfo(Model.DB.tb_TaskInfo tb_TaskInfo)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _SqlScript = string.Empty;
            try
            {
                new tb_TaskDetails().AddTaskDeatils(tb_TaskInfo.TaskDetails);

                _SqlScript += "Insert Into tb_TaskInfo (Id,Title,[Desc],UserId,[Status],CreateTime)";
                _SqlScript += $"    values ('{tb_TaskInfo.Id}','{tb_TaskInfo.Title}','{tb_TaskInfo.Desc}',{tb_TaskInfo.UserId},{tb_TaskInfo.Status},'{tb_TaskInfo.CreateTime}')";
                _SqlDb = Static.Methods.OpenDataBase();
                _SqlDb.UpdateSqlScript(_SqlScript);

            }
            catch (Exception ex)
            {
                throw new Exception("添加任务失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal void DeleteForTaskID(List<string> ListTaskId)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _TaskIds = string.Empty;
            string _SqlScript = string.Empty;
            try
            {
                _SqlDb = Static.Methods.OpenDataBase();
                _TaskIds = Static.Methods.CombiningStrings1<string>(ListTaskId);

                _SqlScript = "Delete From tb_TaskInfo";
                _SqlScript += $" Where Id in ( {_TaskIds} )";

                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("删除任务失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal void updateStatus(string taskId, int status)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                string sqlScript = string.Format("update tb_TaskInfo set Status = {1} where Id = '{0}';", taskId,status);
                sqlDb = Static.Methods.OpenDataBase();
                sqlDb.UpdateSqlScript(sqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("更新任务状态失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        internal void UpdateTaskInfo(Model.DB.tb_TaskInfo taskInfo)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _SqlScript = string.Empty;
            try
            {
                _SqlScript = $"  Update [tb_TaskInfo] Set [Title]='{taskInfo.Title}',[Desc] = '{taskInfo.Title}'";
                _SqlScript += $"  Where [Id] = '{taskInfo.Id}'";


                new tb_TaskDetails().DeleteForTaskID(new List<string>() { taskInfo.Id });
                new tb_TaskDetails().AddTaskDeatils(taskInfo.TaskDetails);

                _SqlDb = Static.Methods.OpenDataBase();
                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("修改任务失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        private List<Model.DB.tb_TaskInfo> DataTableToList(DataTable dataTable)
        {
            List<Model.DB.tb_TaskInfo> _Result;
            Model.DB.tb_TaskInfo _Temp;
            try
            {
                _Result = new List<Model.DB.tb_TaskInfo>();
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    _Temp = new Model.DB.tb_TaskInfo();
                    _Temp.Id = dataTable.Rows[i]["Id"].ToString();
                    _Temp.Title = dataTable.Rows[i]["Title"].ToString();
                    _Temp.Desc = dataTable.Rows[i]["Desc"].ToString();

                    _Temp.UserId = Convert.ToInt32(dataTable.Rows[i]["UserId"].ToString());
                    _Temp.sUserName = new DataAccess.tb_User().GetForUserId(_Temp.UserId).userName;

                    _Temp.Status = Convert.ToInt32(dataTable.Rows[i]["Status"].ToString());
                    _Temp.sStatus = Relations.Common.Methods.GetEnumDescription((Model.SystemEnum.TaskStatus)_Temp.Status);

                    if (!string.IsNullOrEmpty(dataTable.Rows[i]["CreateTime"].ToString()))
                        _Temp.CreateTime = DateTime.Parse(dataTable.Rows[i]["CreateTime"].ToString()).ToString(Static.ParaInfo.DateFormatLong);

                    _Result.Add(_Temp);
                }
                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("将Datatable转换成List<Model.DB.tb_RoleInfo>有异常：" + ex.ToString());
            }
        }
    }
}